package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pojo.Customer;
import utils.DBUtil;

public class CustomerDaoImpl implements CustomerDao {

	@Override
	public int addCustomer(Customer c) {
		String sql = "insert into customer(id,name,salary,sex,birth,favs) values(seq03.nextval,?,?,?,?,?)";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, c.getName());
			pst.setDouble(2, c.getSal());
			pst.setBoolean(3, c.isSex());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			pst.setString(5, c.getFavs());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public List<Customer> loadAllCustomers() {
		List<Customer> list=new ArrayList<Customer>();
		String sql = "select * from customer";
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			
			rs = pst.executeQuery();
			while(rs.next()) {
				Customer c=new Customer();
				int id=rs.getInt("id");
				String name = rs.getString("name");
				double sal = rs.getDouble("salary");
				boolean sex = rs.getBoolean("sex");
				Date birth = new Date(rs.getDate("birth").getTime());
				String favsStr = rs.getString("favs");
				c.setId(id);
				c.setName(name);
				c.setSal(sal);
				c.setSex(sex);
				c.setBirth(birth);
				c.setFavs(favsStr);
				list.add(c);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(con, pst, rs);
		}
		return list;
	}

	@Override
	public int deleteCustomer(Integer id) {
		String sql = "delete customer where id=?";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public int updateCustomer(Customer c) {
		String sql = "update customer set name=?,salary=?,sex=?,birth=?,favs=? where id=?";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, c.getName());
			pst.setDouble(2, c.getSal());
			pst.setBoolean(3, c.isSex());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			pst.setString(5, c.getFavs());
			pst.setInt(6, c.getId());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return m;
	}

	@Override
	public Customer loadCustomer(Integer id) {
		Customer c=null;
		
		String sql = "select * from customer where id=?";
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);;
			rs = pst.executeQuery();
			if (rs.next()) {
				c = new Customer();
				int cid=rs.getInt("id");
				String name = rs.getString("name");
				double sal = rs.getDouble("salary");
				boolean sex = rs.getBoolean("sex");
				Date birth = new Date(rs.getDate("birth").getTime());
				String favsStr = rs.getString("favs");
				c.setId(cid);
				c.setName(name);
				c.setSal(sal);
				c.setSex(sex);
				c.setBirth(birth);
				c.setFavs(favsStr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return c;
	}

}
